﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.JsonPatch.Adapters;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using VOL.Core.DBManager;
using Dapper;
using System.Data;
using VOL.MyTable.Services;

namespace VOL.WebApi.Controllers.MyTable.bigscreen
{
    [Route("api/bigscreen")]
    [ApiController]
    public class BigscreenController : Controller
    {
        [HttpPost, HttpGet, Route("get_shuj"), AllowAnonymous]
        public object get_shuj()
        {
            Hashtable rtn_hst = new Hashtable();
            string sql = "";
            //销售订单明细
            sql = "select derive_material_code,(select material_name from TM_VHCL_MATERIAL where material_code=derive_material_code) mat_name,chassis_remark chassis_remark,to_char(check_date, 'yyyy-mm-dd') check_date ,b.delivery_address,b.ORDER_NO,(select dealer_shortname from tm_dealer where dealer_id = b.dealer_id) dealer_shortname,b.order_quantity,b.ORDER_AMOUNT from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  and to_char(check_date, 'yyyymm') >= TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm') and to_char(check_date, 'yyyymm')<=TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm')+11  and b.ORDER_AMOUNT>0 order by check_date desc";
            sql = sql.Replace("'", "''");
            //List<dynamic> dditem=Other_Oracle_Helper.query<dynamic>(sql, "dms");//订单明细,通过第三方连接取
            List<dynamic> dditem = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery(DMS,'" + sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警

            //接单趋势
            sql = "select  numa,dat from (select sum(b.ORDER_QUANTITY) - sum(nvl(b.cancel_Quantity, 0)) numa, to_char(check_date, 'yyyymm') dat from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  group by to_char(check_date, 'yyyymm') order by to_char(check_date, 'yyyymm') desc ) tab1 where rownum <= 17 order by dat ";
            sql = sql.Replace("'", "''");
            List<dynamic> jdqs = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery(DMS,'" + sql + "')", "").ToList(); ;//接单趋势图,通过dblink取数，SQL运行的是oracle预警
            int y_s_total_num = 0;//本年销售接单
            int m_s_total_num = 0;//本月销售接单
            if (jdqs.Count > 0)
            {
                List<dynamic> jdqs_by = jdqs.Where(t => Convert.ToString(t.DAT).Substring(0, 4) == DateTime.Now.ToString("yyyy")).ToList();
                if (jdqs_by.Count > 0) y_s_total_num = jdqs_by.Sum(t => int.Parse(t.NUMA)); //本年销售接单总数

                List<dynamic> jdqs_bm = jdqs.Where(t => Convert.ToString(t.DAT).Substring(0, 6) == DateTime.Now.ToString("yyyyMM")).ToList();
                if (jdqs_bm.Count > 0) m_s_total_num = jdqs_bm.Sum(t => int.Parse(t.NUMA)); //本月销售接单总数
            }


            //本年累计生产下线
            sql = "  select substr(proddate, 1, 4) dat, count(distinct vin) sum from prodplan t where offmark = '*' and to_char(sysdate, 'yyyy') = substr(proddate, 1, 4)  and lineno = '100' group by substr(proddate, 1, 4) order by substr(proddate, 1, 4) asc";
            sql = sql.Replace("'", "''");
            List<dynamic> y_off = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList(); ;//本年累计下线
            //Console.WriteLine(GET_SQL_TXT_FROM_DB("8"));

            //本月生产计划和每日生产计划
            sql = " select substr(proddate,1,8) dat,NVL(sum(num),0) sum from seqplan t where to_char(sysdate,'yyyymm')=substr(proddate,1,6) and lineno in(100) group by substr(proddate,1,8),lineno order by substr(proddate,1,8)";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_plan = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList(); ;//本月生产计划和每日生产计划
            int m_total_plan = m_plan.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_plan.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_plan = m_plan.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_plan = 0;
            if (d_plan.Count > 0) curr_day_plan = int.Parse(d_plan[0].SUM);
            // Console.WriteLine(DateTime.Now.ToString("yyyyMMdd"));


            //本月、今日上线
            sql = " select substr(ontime,1,8) dat,count(*) sum from prodplan where to_char(sysdate,'yyyymm')=substr(ontime,1,6) and lineno in(100) and onmark='*' group by substr(ontime,1,8) order by substr(ontime,1,8)";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_on = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList();
            int m_total_on = m_on.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_on.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_on = m_on.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_on = 0;
            if (d_on.Count > 0) curr_day_on = int.Parse(d_on[0].SUM);

            //本月、今日下线
            sql = "select substr(OFFtime,1,8) dat,count(distinct vin) sum from prodplan t where offmark='*' and to_char(sysdate,'yyyymm')=substr(OFFtime,1,6) and lineno='100' group by substr(OFFtime, 1, 8) order by substr(OFFtime, 1, 8) asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_off = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList();
            int m_total_off = m_off.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_off.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_off = m_off.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_off = 0;
            if (d_off.Count > 0) curr_day_off = int.Parse(d_off[0].SUM);

            //销售发车趋势
            sql = "select nvl(t1.countf,0)-nvl(t2.countr, 0) numa,t1.dat from(select count(*) as countf,substr(wadat_ist, 1, 6) dat from bi_wbstk_sap t where lfart = 'YCLF' group by substr(wadat_ist, 1, 6) order by dat desc) t1,(select count(*) as countr,substr(wadat_ist, 1, 6) dat from bi_wbstk_sap t where lfart = 'YCLR' group by substr(wadat_ist, 1, 6) order by dat desc) t2 where t1.dat = t2.dat(+) and rownum<= 17  order by dat";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> sale_fa = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([117],'" + sql + "')", "").ToList(); ;
            int y_sf_total_num = 0;//本年销售发车
            int m_sf_total_num = 0;//本月销售发车
            if (sale_fa.Count > 0)
            {
                List<dynamic> sale_fa_by = sale_fa.Where(t => Convert.ToString(t.DAT).Substring(0, 4) == DateTime.Now.ToString("yyyy")).ToList();
                if (sale_fa_by.Count > 0) y_sf_total_num = sale_fa_by.Sum(t => int.Parse(t.NUMA)); //本年销售发车总数

                List<dynamic> sale_fa_bm = sale_fa.Where(t => Convert.ToString(t.DAT).Substring(0, 6) == DateTime.Now.ToString("yyyyMM")).ToList();
                if (sale_fa_bm.Count > 0) m_sf_total_num = sale_fa_bm.Sum(t => int.Parse(t.NUMA)); //本月销售发车总数
            }


            //销售实销趋势
            sql = "select numa, dat from(SELECT count(distinct A.VIN) numa, TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') dat   FROM TM_SV_VEHICLE A WHERE A.CAR_STATUS = 10641006   AND A.IS_DELETE = 0  group by TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') order by TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') desc) where rownum<= 12 order by dat asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> sale_sx = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([DMS],'" + sql + "')", "").ToList(); ;
            int y_sx_total_num = 0;//本年销售发车
            int m_sx_total_num = 0;//本月销售发车
            if (sale_sx.Count > 0)
            {
                List<dynamic> sale_sx_by = sale_sx.Where(t => Convert.ToString(t.DAT).Substring(0, 4) == DateTime.Now.ToString("yyyy")).ToList();
                if (sale_sx_by.Count > 0) y_sx_total_num = sale_sx_by.Sum(t => int.Parse(t.NUMA)); //本年销售实销总数

                List<dynamic> sale_sx_bm = sale_sx.Where(t => Convert.ToString(t.DAT).Substring(0, 6) == DateTime.Now.ToString("yyyyMM")).ToList();
                if (sale_sx_bm.Count > 0) m_sx_total_num = sale_sx_bm.Sum(t => int.Parse(t.NUMA)); //本月销售实销总数
            }

            //本月各个车间生产计划，100总装，200车架，300涂装，400焊装，800内饰
            sql = " select substr(proddate,1,6) dat,sum(num) sum,lineno from seqplan t where to_char(sysdate,'yyyymm')<=substr(proddate,1,6) and lineno in(100,200,300,400,800) group by substr(proddate,1,6),lineno order by substr(proddate,1,6)";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> fact_plan = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList();


            //整车存量资金
            sql = "select b.dat dat, round(b.num / a.num, 4) bi,round(b.num / 10000, 2) sum1,round(a.num / 10000, 2) sum2 from BI_Pd_zc_SAP a, BI_Pd_SAP b where a.dat = b.dat and to_char(sysdate-6 * 30,'yyyymm')<= b.dat order by dat asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> ze_c_je = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([117],'" + sql + "')", "").ToList();

            //当前生产状态
            sql = "  select tt2.order_state models, nvl(tt1.co, 0) co  from (select order_state, ym, count(*) co from (select substr(YEAR_M, 1, 4) ym, YEAR_M, order_state   from VW_ORDERVIEW_JEEFW  where substr(YEAR_M, 1, 4) = to_char(sysdate,'yyyy')) tt  group by order_state, ym) tt1,  (select state order_state,orderid from JEEFW_ORDERCYCLE_STATE) tt2  where tt2.order_state = tt1.order_state(+) order by orderid asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> k_type_bl = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + sql + "')", "").ToList();


            rtn_hst.Add("data", dditem);//销售接单明细
            rtn_hst.Add("jdqs", jdqs);//销售接单趋势
            rtn_hst.Add("sale_fa", sale_fa);//销售发车趋势

            rtn_hst.Add("y_off", y_off);//本年累计下线
            rtn_hst.Add("m_total_plan", m_total_plan);//本月总生产计划
            rtn_hst.Add("curr_day_plan", curr_day_plan);//当日生产计划

            rtn_hst.Add("m_total_on", m_total_on);//本月总上线数量
            rtn_hst.Add("curr_day_on", curr_day_on);//当日上线数量

            rtn_hst.Add("m_total_off", m_total_off);//本月总下线数量
            rtn_hst.Add("curr_day_off", curr_day_off);//当日下线数量

            rtn_hst.Add("y_s_total_num", y_s_total_num);//本年销售接单总数
            rtn_hst.Add("m_s_total_num", m_s_total_num);//本月销售接单总数

            rtn_hst.Add("y_sf_total_num", y_sf_total_num);//本年销售发车总数
            rtn_hst.Add("m_sf_total_num", m_sf_total_num);//本月销售发车总数

            rtn_hst.Add("y_sx_total_num", y_sx_total_num);//本年销售实销总数
            rtn_hst.Add("m_sx_total_num", m_sx_total_num);//本月销售实销总数

            rtn_hst.Add("fact_plan", fact_plan);//本月各个车间生产计划
            rtn_hst.Add("ze_c_je", ze_c_je);//整车存量资金
            rtn_hst.Add("k_type_bl", k_type_bl);//库存车类型占比

            return rtn_hst;
        }
        /// <summary>
        ///  获取数据库的共享SQL语句,只能取117数据库的数据
        /// </summary>
        /// <param name="sql_id"></param>
        /// <returns></returns>
        private static string GET_SQL_TXT_FROM_DB(string sql_id)
        {
            string oralce_sql = "select * from jrorder.T_execute_sql where sql_id=@sql_id@";
            oralce_sql = oralce_sql.Replace("@sql_id@", sql_id).Replace("'", "''");
            List<dynamic> rtnitem = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([117],'" + oralce_sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警
            if (rtnitem.Count > 0)
            {
                return rtnitem[0].SQL_TEXT;
            }
            return "";
        }
        [HttpPost, HttpGet, Route("get_api_test"), AllowAnonymous]
        public object get_api_test()
        {
            Hashtable rtn_hst = new Hashtable();
            string rtnstring = wfoa_tools.get_realusername("xianshun.huang");
            int? rtnstring2 = wfoa_tools.get_oayidepidfrom_userid("jianguo.chen");//("zhanwen.li");
            string rtnstring3 = wfoa_tools.get_realusername(wfoa_tools.get_ggld_from_userid("zhanwen.li").ToString());
            string rtnstring4 = wfoa_tools.get_bmfzr_from_depidoa(30).ToString();
            string rtnstring5 = wfoa_tools.get_ggld_from_userid("xianshun.huang");

            rtn_hst.Add("test1", rtnstring);
            rtn_hst.Add("test2", rtnstring2);
            rtn_hst.Add("tes32", rtnstring3);
            rtn_hst.Add("rtnstring4", rtnstring4);
            rtn_hst.Add("rtnstring5", rtnstring5);
            return rtn_hst;
        }

        [HttpPost, HttpGet, Route("get_api_test_with"), AllowAnonymous]
        public object get_api_test_with(int reimid,int curr_stepid)
        {
            Hashtable rtn_hst = new Hashtable();
            int ?rtn=wfoa_tools.get_next_step(reimid, curr_stepid);
            rtn_hst.Add("rtn", rtn);
            return rtn_hst;
        }
    }
}
